import java.util.ArrayList;
import java.util.HashSet;
import java.util.Hashtable;
import java.util.LinkedList;
import java.util.Set;
import java.io.BufferedReader;
//import java.io.File;
//import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.sql.*;
import java.util.Iterator;

//import javax.xml.parsers.DocumentBuilder;
//import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;

//import org.w3c.dom.Document;
//import org.w3c.dom.Element;
//import org.w3c.dom.Node;
//import org.w3c.dom.NodeList;
import org.xml.sax.SAXException;

class ConditionClause {
	public String param;
	public String condition;
	
	public ConditionClause() {
		this.param = this.condition = "";
	}
	
	public ConditionClause(String param, String condition) {
		this.param = param;
		this.condition = condition;
	}
}

public class ConnectDB {
	
	protected static String DBServer 	   = "//149.171.40.42:3306/";
	protected static String DBUser   	   = "reader";
	protected static String DBPsword 	   = "test123?";
	protected static String DBDatabase   = "fedora22";
	protected static String MappingErrorMsg = "Mapping can't be found";
	
	//protected static final String MultiFields  = "dcCreator";	
	protected static final String MultiField  = "dcPublisher";
	protected static final String Pid		   = "pid";
	
	protected Hashtable<String, String> Conjunctions  = new Hashtable<String, String>();
	protected Hashtable<String, String> Tables = new Hashtable<String, String>();
	protected LinkedList<ConditionClause> Conditions = new LinkedList<ConditionClause>();
	protected String ConditionString = "";
	protected ArrayList<String> Fields = new ArrayList<String>();
	//protected SortingOrder Orders = new SortingOrder();
	protected LinkedList<SortingOrder> Orders = new LinkedList<SortingOrder>();    
	protected Hashtable<String, String> MultiOrders = new Hashtable<String, String>();
	protected Connection Conn 		= null;
	protected ResultSet ResultSet 	= null;
	protected Statement Stmt		= null;
	private Hashtable<String, String> Mapping = new Hashtable<String, String>();
//	private Boolean ContainMultiFields = false;
	protected String Result[] = null;
	protected String MultipleResults[] = null;
//	private SearchResult Results[] = null;
	private Set<String> MultiFields = new HashSet<String>();
	private String Limit[] = null;
	private String TotalRecord = "0";
	private String MultiFieldsDisjoint = "[repository_disjoint_point]";
	
	public ConnectDB()
	{
		System.out.println("TEST CONNECTDB!");		
	}
	
	public void setMultiFieldsDisjoint(String str) {
		this.MultiFieldsDisjoint = str;
	}
	
	public void connDB() throws ClassNotFoundException, SQLException {
		
		Class.forName("com.mysql.jdbc.Driver");
		String connUrl = "jdbc:mysql:" + 
						  DBServer + 
						  DBDatabase;			
		Conn = DriverManager.getConnection(connUrl, DBUser, DBPsword);		
	}
	
	public void initDB() {
		;
	}
	
	private String getMappingField(String object) throws MP2Exception {
		if ((this.Mapping.get(object) == null) || (this.Mapping.get(object) == "")) {
			throw  new MP2Exception();
		}
		else
			return this.Mapping.get(object); 
	}
	
	public void setValidRecordsOnly() {
		//state not like "i"
		String validFilter = "state";
		String validCondition = " not like \"i\" ";
		if (!this.ConditionString.trim().equals(""))
			this.ConditionString += " and " + validFilter + validCondition;
		else
			Conditions.add(new ConditionClause(validFilter, validCondition));
	}
	
	public void addConjunction(int index, String conjunction) {
		Conjunctions.put(index + "", conjunction);
	}
	
	public void setType(String viaType) throws MP2Exception {
		String type = "type";
		if (viaType.equalsIgnoreCase("book")) {	//hard code in			
			Conditions.add(new ConditionClause(this.getMappingField(type), " not like \"%book chapter%\" "));					
		}
		Conditions.add(new ConditionClause(this.getMappingField(type), setWildcard(viaType)));
	}
	
	public void setTypes(String types[]) throws MP2Exception {
		String type = "type";
		String cond = "";
		String glue = " or ";
		for (int i = 0; i < types.length; i++) {
			if (types[i].equalsIgnoreCase("book")) { //hard code in
				cond += " ( " + this.getMappingField(type) + " " + " not like \"%book chapter%\" and " + 
						this.getMappingField(type) + " " + setWildcard(types[i]) + " ) " ;
			} else {
				cond += this.getMappingField(type) + " " + setWildcard(types[i]);
			}
			cond += glue;
		}
		if (cond.endsWith(glue))
			cond = cond.substring(0, cond.length() - glue.length());
		cond = " ( " + cond + " )";
		Conditions.add(new ConditionClause(cond, ""));
	}
	
	public void setMapping(String file) throws IOException {
		this.mappingToDB(file);
	}
	
	//why do separate instead of use a general function? 
	// -> each time you have to visit all the nodes to get one leaf.
	public void settingDBConf(String file) throws ParserConfigurationException, SAXException, IOException {		
		String rootTag 			= "module";
		String identifier 		= "name";
		String pointer	  		= "publicationlist";
		
		Configuration cf = new Configuration(file,rootTag,identifier,pointer);
		String stops[] = { "dbserver", "dbuser", "dbpassword", "dbschema" };
		String results[] = cf.readFromConfig(stops);
		DBServer = results[0];
		DBUser = results[1];
		DBPsword = results[2];
		DBDatabase = results[3];		
		return;
	}
	/*
	public void settingDBConf(String file) {
		
		String rootTag 			= "module";
		String identifier 		= "name";
		String pointer	  		= "publicationlist";	
		String stopOne	  		= "dbserver";
		String stopTwo	  		= "dbuser";
		String stopThree	  	= "dbpassword";
		String stopFour	  		= "dbschema";		
		
		try {
			DocumentBuilderFactory docFactory = DocumentBuilderFactory.newInstance();
			DocumentBuilder docBuilder = docFactory.newDocumentBuilder();
			Document doc = docBuilder.parse(new File(file));
			doc.getDocumentElement().normalize();
			NodeList nodes = doc.getElementsByTagName(rootTag);
			for (int i = 0; i < nodes.getLength(); i++) {
				Node node = nodes.item(i);				
				if (node.getNodeType() == Node.ELEMENT_NODE) {
					Element element = (Element) node;
					NodeList id	  = element.getElementsByTagName(identifier);					
					if (((((Node) id.item(0)).getChildNodes()).item(0).getNodeValue().trim()).equalsIgnoreCase(pointer)) {						
						id = element.getElementsByTagName(stopOne);
						DBServer = (((Node) id.item(0)).getChildNodes()).item(0).getNodeValue().trim();
						id = element.getElementsByTagName(stopTwo);
						DBUser = (((Node) id.item(0)).getChildNodes()).item(0).getNodeValue().trim();
						id = element.getElementsByTagName(stopThree);
						DBPsword = (((Node) id.item(0)).getChildNodes()).item(0).getNodeValue().trim();
						id = element.getElementsByTagName(stopFour);
						DBDatabase = (((Node) id.item(0)).getChildNodes()).item(0).getNodeValue().trim();
					}
				}
			}
		} catch (Exception e) {System.out.println(e.getMessage());}
		return;
	}
	*/
	
	public void setupMultiFiltersQueryString(String filters, String value, String delimiter) throws MP2Exception {
		String or	= " or ";
		String[] trunk = filters.split(delimiter);
		String sql = "";
		for (int i = 0; i < trunk.length; i++) {
			sql += this.getMappingField(trunk[i]) + ConnectDB.setWildcard(value) + or;
		}
		if (sql.endsWith(or))
			sql = sql.substring(0, sql.length() - or.length());
		//return sql;
		Conditions.add(new ConditionClause(sql, ""));
	}
	
	public void settingDBEnvir(String file) throws IOException {
		BufferedReader br = new BufferedReader(new FileReader(file));
		String line;
		String currLine[] = null;
		while ((line = br.readLine()) != null) {				
			if (line.trim().isEmpty() || (line.trim().charAt(0) == '#'))
				continue;
			currLine = line.split("=", 2);
			if (currLine.length != 2)
				continue;
			Mapping.put(currLine[0].trim(), currLine[1].trim());				
		}
	}
	
	private void mappingToDB(String file) throws IOException {
		BufferedReader br = new BufferedReader(new FileReader(file));
		String line;
		String currLine[] = null;
		while ((line = br.readLine()) != null) {				
			if (line.trim().isEmpty() || (line.trim().charAt(0) == '#'))
				continue;
			currLine = line.split("=", 2);
			if (currLine.length != 2)
				continue;
			Mapping.put(currLine[0].trim(), currLine[1].trim());				
			this.setupMultiFields(currLine[0].trim(), currLine[1].trim());
		}
	}

	private void setupMultiFields(String field, String mapping) {
		if (mapping.equals(MultiField)) {
			MultiFields.add(field);
		}
	}
	
	public static String setWildcard(String value) {
		return " like \"%" + value + "%\"";
	}
	
	public static String setEqual(String value) {
		return " = \"" + value + "\"";
	}
	
	protected void setTable(String table, String alias) {
		Tables.put(table, alias);
	}
	
	public static String setOtherConditions(String value, String condition) {
		return condition + " \"" + value + "\"";
	}
	
	public void setFieldsCondition(String field, String clause) throws MP2Exception  {		
		Conditions.add(new ConditionClause(this.getMappingField(field), clause));
	}
	
	public void setFieldsCondition(String[] fields, String value) throws MP2Exception  {
		String cond = "";
		String glue = " or ";
		for (int i = 0; i < fields.length; i++) 
			cond += this.getMappingField(fields[i]) + " " + setWildcard(value) + glue;
		if (cond.endsWith(glue))
			cond = cond.substring(0, cond.length() - glue.length());
		Conditions.add(new ConditionClause(cond, ""));
	}

	protected void setCondition(String field, String clause, String tableAlias, String operator) {		
		return;
	}
	/*
	public void setOrder(SortingOrder so) throws MP2Exception {
		if (isOrderEmpty(so) == 0)
			return;
		else if (isOrderEmpty(so) == 1) {
			this.Orders.sort = this.getMappingField(so.sort);
			this.Orders.order = this.getSortOrder(so.order);
		} else if (isOrderEmpty(so) == 2) {
			this.Orders.sort = this.getMappingField(so.sort);
			this.Orders.order = this.getSortOrder(so.order);
			this.Orders.subSort = this.getMappingField(so.subSort);
			this.Orders.subOrder = this.getSortOrder(so.subOrder);
		} else 
			 return;
	}
	*/
	public void setOrder(LinkedList<SortingOrder> so) throws MP2Exception {
		Iterator<SortingOrder> iter = so.iterator();		
		while (iter.hasNext()) {
			SortingOrder obj = iter.next();
			this.Orders.add(new SortingOrder(this.getMappingField(obj.sort), this.getSortOrder(obj.order)));
		}
	}
	
	private String getSortOrder(String isASC) {
		String asc = "ASC";
		String desc = "DESC";
		if (isASC.equalsIgnoreCase(asc))
			return asc;
		else 
			return desc;
	}
	
	
	/*
	public void setOrder(SortingOrder so) throws MP2Exception {		
		this.Orders = so;
		
		if ((this.Orders.sort == null) || (this.Orders.sort == ""))
			return;
		this.addFields(this.getMappingField(this.Orders.sort));
		
		if ((this.Orders.order == null) && (this.Orders.order == ""))
			this.Orders.order = this.setSortOrder("asc");
		else 
			this.Orders.order = this.setSortOrder(this.Orders.order);
				
		if (((String) this.getMappingField(this.Orders.sort)).equalsIgnoreCase(MultiField)) {
			this.MultiOrders.put(this.Orders.sort, "sort");			
			this.ContainMultiFields = true;
		}
		
		if ((this.Orders.subSort == null) || (this.Orders.subSort == ""))
			return;		
		this.addFields(this.getMappingField(this.Orders.subSort));
		
		if ((this.Orders.subOrder == null) && (this.Orders.subOrder == ""))
			this.Orders.subOrder = this.setSortOrder("asc");
		else 
			this.Orders.subOrder = this.setSortOrder(this.Orders.subOrder);
		
		if (this.getMappingField(this.Orders.subSort).equalsIgnoreCase(MultiField)) {
			this.MultiOrders.put(this.Orders.subSort, "sub");			
			this.ContainMultiFields = true;
		}
		
		return;
	}
	
	private String setSortOrder(String order) {
		if (order.equalsIgnoreCase("ASC"))			
			return "<";
		else
			return ">";
	}
	*/
	/*
	private int isOrderEmpty(SortingOrder so) {		
		if ((so.sort == "") && (so.subSort == ""))
			return 0;
		else if ((so.sort != "") && (so.subSort == ""))
			return 1;
		else if ((so.sort != "") && (so.subSort != ""))
			return 2;
		return -1;
	}*/
	
	public void setTable() {
		this.setTable("doFields", "d");
	}
	
	public void addField(String field) throws MP2Exception {
		this.addPidField();
		String dbField = this.getMappingField(field);
		if (!Fields.contains(dbField))
			Fields.add(dbField);
	}
	
	public void addPidField() {
		if (!Fields.contains(Pid))
			Fields.add(Pid);		
	}
	
	protected String[] pileUp(Hashtable<String, String> tb){
		String pile[] = new String[tb.size()];
		
		Iterator<String> iter = tb.keySet().iterator();		
		for (int i = 0; iter.hasNext(); i++) {
			Object key = iter.next();
			pile[i] = key.toString() + " " + tb.get(key);
		}
		return pile;
	}
	
	protected String join(String in[], String delimiter) {
		String result = "";
		for (int i = 0; i < in.length; i++) {
			result += in[i] + delimiter;
		}
		if (result.endsWith(delimiter))
			result = result.substring(0, result.length() - delimiter.length());
		return result;
	}
	
	private String pileUp(Hashtable<String, String> tb, String delimiter) {
		String str = "";
		Iterator<String> iter = tb.keySet().iterator();
		for (int i = 0; iter.hasNext(); i++) {
			Object key = iter.next();
			str += key.toString() + " " + tb.get(key) + delimiter;
		}
		if (str.endsWith(delimiter))
			str = str.substring(0, str.length() - delimiter.length());
		return str;
	}
	
	public String getConditions() {
		Iterator<ConditionClause> iter = Conditions.iterator();
		String str = "";
		String delimiter = " and ";
		String joint	 = "";
		int index = 0;
		while (iter.hasNext()) {
			ConditionClause obj = iter.next();
			String cond = " ( " + obj.param + " " + obj.condition + " ) ";
			if (index == 0)
				;
			else if (Conjunctions.get(index + "") != null) {
				joint =  " " + Conjunctions.get(index + "") + " ";
			}
			else {
				joint = delimiter;//str += delimiter;
			}
			str += joint + cond;
			index++;
		}
		return str;
	}
	
	protected String getFields() {
		if (Fields.size() == 0)
			this.addPidField();
		Iterator<String> iter = Fields.iterator();
		String fields = " ";
		while (iter.hasNext()) {
			Object field = iter.next();
			fields += field.toString() + ",";
		}
		return fields.substring(0, fields.length() - 1) + " ";
	}
	
	protected String getOrders() {
		String orders = "";
		String delimiter = ",";
		String orderBy = "";
		Iterator<SortingOrder> iter = this.Orders.iterator();
		while (iter.hasNext()) {
			orderBy = " order by ";
			SortingOrder obj = iter.next();
			orders += obj.sort + " " + obj.order + delimiter;
		}
		if (orders.endsWith(delimiter))
			orders = orders.substring(0, orders.length() - delimiter.length());
		return orderBy + orders;
	}
	
	public void setConditionString(String cond) {
		this.ConditionString = cond;
	}
	
	public void setLimit(String limit[]) {
		this.Limit = limit;
	}
	
	public boolean sendSQL() throws SQLException {		
		
		Stmt = Conn.createStatement();		
		String sql = "Select " + this.getFields();
		this.setValidRecordsOnly();
		String conds  = (this.ConditionString.equals("")) ? this.getConditions() : this.ConditionString;
		sql += " from " + this.pileUp(Tables, ", ") + " ";
		if (conds.length() > 0)
			sql += " where " + conds + " ";		
		String orders = this.getOrders();
		if (orders.length() > 0)
			sql += " " + orders;
		if (this.Limit != null)
			sql += " LIMIT " + this.Limit[0] + ", " + this.Limit[1];
		
		if (TestCases.showSQL)
		System.out.println("[Query]: " + sql);
		ResultSet = Stmt.executeQuery(sql);
		return true;
	}
	
	public String getTotalRecordsRelated() throws SQLException {
		String countTotal = " count(*) ";
		Stmt = Conn.createStatement();
		String sql = "Select " + countTotal;
		this.setValidRecordsOnly();
		String conds  = (this.ConditionString.equals("")) ? this.getConditions() : this.ConditionString;
		sql += " from " + this.pileUp(Tables, ", ") + " ";
		if (conds.length() > 0)
			sql += " where " + conds + " ";
		System.out.println("[Query]: " + sql);
		ResultSet = Stmt.executeQuery(sql);
		
		if (ResultSet.next()) 
			TotalRecord = ResultSet.getString(1);
		
		return TotalRecord;
	}
	
	private void getResults() throws SQLException {
/*		if (this.ResultSet.last()) {
			//System.out.println("get results: " + this.ResultSet.getRow() + " rows");
			this.Result = new String[this.ResultSet.getRow()];
			this.ResultSet.beforeFirst();
		} else {
			return;
		}
		for (int i = 0; this.ResultSet.next(); i++)
			this.Result[i] = this.ResultSet.getString(Pid);
	}
	
	public String[] getResultSets() throws SQLException {
		if (this.ResultSet == null)
			return null;
		//this.sortResults();
		this.getMultipleResultSets();
		return this.Result;
	}
	
	private void getMultipleResultSets() throws SQLException {
*/	
		//String joint = this.MultiFieldsDisjoint;	//in future use base64.
		if (this.ResultSet.last()) {
			this.Result = new String[this.ResultSet.getRow()];
			this.MultipleResults = new String[this.ResultSet.getRow()];
			this.ResultSet.beforeFirst();
		} else {
			return;
		}
		for (int i = 0; this.ResultSet.next(); i++) {
			this.Result[i] = this.ResultSet.getString(1);
			this.MultipleResults[i] = "";
			for (int j = 1; j <= Fields.size(); j++) {
				this.MultipleResults[i] += this.ResultSet.getString(j) + MultiFieldsDisjoint;
			}
			if (this.MultipleResults[i].endsWith(MultiFieldsDisjoint))
				this.MultipleResults[i] = this.MultipleResults[i].substring(0, 
												this.MultipleResults[i].length() - MultiFieldsDisjoint.length());
		}
	}

	public String[] getMultipleResults() {
		return this.MultipleResults;
	}
	
	public String[] getResultSet() throws SQLException {
		if (this.ResultSet == null)
			return null;
		//this.sortResults();
		this.getResults();
		return this.Result;
	}
	
	public void clean() throws SQLException {
		this.cleanResource();
		this.Conn.close();
	}
	
	public void cleanResource() throws SQLException {
		Conditions  = new LinkedList<ConditionClause>();
		Conjunctions = new Hashtable<String, String>();
		ConditionString = "";
		if (ResultSet != null)
			ResultSet.close();
		if (Stmt != null)
			Stmt.close();
		Result 		= null;
		MultipleResults = null;
		TotalRecord = "0";
		Limit		= null;
//		Results     = null;
	}
}